USE [NGroup_VTL_DB]
GO

/****** Object:  StoredProcedure [dbo].[GetHomePageProductList]    Script Date: 8/16/2014 4:35:38 PM ******/
DROP PROCEDURE [dbo].[GetHomePageProductList]
GO

/****** Object:  StoredProcedure [dbo].[GetHomePageProductList]    Script Date: 8/16/2014 4:35:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Bui Thanh Nam
-- Create date: 20131222
-- Description:	Get list of products to show in homepage
-- =============================================
CREATE PROCEDURE [dbo].[GetHomePageProductList]
	@Lang		INT = 1
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;	

	DECLARE @EnCode INT = 2
	
	SELECT [ID]
	INTO #INACTIVE_TYPES
	FROM [dbo].[GenericTypes]
	WHERE [IsActive] = 0

	-- San pham moi
	SELECT 
		CASE @Lang WHEN @EnCode THEN [dbo].[Products].[Title_En] ELSE [dbo].[Products].[Title] END AS [Title],
		CASE @Lang WHEN @EnCode THEN [dbo].[Products].[ImageSmallURL_En] ELSE [dbo].[Products].[ImageSmallURL] END AS [ImageSmallURL],
		CASE @Lang WHEN @EnCode THEN [dbo].[Products].[ImageLargeURL_En] ELSE [dbo].[Products].[ImageLargeURL] END AS [ImageLargeURL]
		,[dbo].[Products].[ID]
		,[dbo].[Products].[TypeID]
		,[dbo].[Products].[SupTypeID]
		,[dbo].[Products].[UniqueString]		 
	FROM [NGroup_VTL_DB].[dbo].[Products] WITH (NOLOCK) INNER JOIN [dbo].[GenericTypes] WITH (NOLOCK)
	ON [dbo].[Products].[TypeID] = [dbo].[GenericTypes].[ID] LEFT JOIN #INACTIVE_TYPES
	ON [dbo].[Products].[SupTypeID] = #INACTIVE_TYPES.[ID] 
	WHERE 
	    #INACTIVE_TYPES.[ID] IS NULL
	    AND [dbo].[Products].[IsActive] <> 0
		AND [HomePageDisplayBlockID] = 1
		AND [dbo].[GenericTypes].[IsActive] <> 0
		ORDER BY [dbo].[Products].[CreatedAt] DESC, [dbo].[Products].[LastUpdatedAt] DESC

	-- Day deo moi
	SELECT
		CASE @Lang WHEN @EnCode THEN [dbo].[Products].[Title_En] ELSE [dbo].[Products].[Title] END AS [Title],
		CASE @Lang WHEN @EnCode THEN [dbo].[Products].[ImageSmallURL_En] ELSE [dbo].[Products].[ImageSmallURL] END AS [ImageSmallURL],
		CASE @Lang WHEN @EnCode THEN [dbo].[Products].[ImageLargeURL_En] ELSE [dbo].[Products].[ImageLargeURL] END AS [ImageLargeURL]
		,[dbo].[Products].[ID]
		,[dbo].[Products].[TypeID]
		,[dbo].[Products].[SupTypeID]
		,[dbo].[Products].[UniqueString]			
	FROM [NGroup_VTL_DB].[dbo].[Products] WITH (NOLOCK) INNER JOIN [dbo].[GenericTypes] WITH (NOLOCK)
	ON [dbo].[Products].[TypeID] = [dbo].[GenericTypes].[ID] LEFT JOIN #INACTIVE_TYPES
	ON [dbo].[Products].[SupTypeID] = #INACTIVE_TYPES.[ID] 
	WHERE 
		#INACTIVE_TYPES.[ID] IS NULL
		AND [dbo].[Products].[IsActive] <> 0		
		AND [HomePageDisplayBlockID] = 2		
		AND [dbo].[GenericTypes].[IsActive] <> 0
		ORDER BY [dbo].[Products].[CreatedAt] DESC, [dbo].[Products].[LastUpdatedAt] DESC

	-- The nhua moi
	SELECT 
		CASE @Lang WHEN @EnCode THEN [dbo].[Products].[Title_En] ELSE [dbo].[Products].[Title] END AS [Title],
		CASE @Lang WHEN @EnCode THEN [dbo].[Products].[ImageSmallURL_En] ELSE [dbo].[Products].[ImageSmallURL] END AS [ImageSmallURL],
		CASE @Lang WHEN @EnCode THEN [dbo].[Products].[ImageLargeURL_En] ELSE [dbo].[Products].[ImageLargeURL] END AS [ImageLargeURL]
		,[dbo].[Products].[ID]
		,[dbo].[Products].[TypeID]
		,[dbo].[Products].[SupTypeID]
		,[dbo].[Products].[UniqueString]			
	FROM [NGroup_VTL_DB].[dbo].[Products] WITH (NOLOCK) INNER JOIN [dbo].[GenericTypes] WITH (NOLOCK)
	ON [dbo].[Products].[TypeID] = [dbo].[GenericTypes].[ID] LEFT JOIN #INACTIVE_TYPES
	ON [dbo].[Products].[SupTypeID] = #INACTIVE_TYPES.[ID] 
	WHERE 
		#INACTIVE_TYPES.[ID] IS NULL
		AND [dbo].[Products].[IsActive] <> 0		
		AND [HomePageDisplayBlockID] = 3		
		AND [dbo].[GenericTypes].[IsActive] <> 0
		ORDER BY [dbo].[Products].[CreatedAt] DESC, [dbo].[Products].[LastUpdatedAt] DESC

	DROP TABLE #INACTIVE_TYPES
	
	-- Tin tuc
	SELECT TOP 2
		CASE @Lang WHEN @EnCode THEN [dbo].[News].[Title_En] ELSE [dbo].[News].[Title] END AS [Title],
		CASE @Lang WHEN @EnCode THEN [dbo].[News].[ImageSmallURL_En] ELSE [dbo].[News].[ImageSmallURL] END AS [ImageSmallURL],
		CASE @Lang WHEN @EnCode THEN [dbo].[News].[BriefContent_En]  ELSE [dbo].[News].[BriefContent]  END AS [Content]
		,[dbo].[News].[ID]
		,[dbo].[News].[TypeID]
		,[dbo].[News].[UniqueString]
	FROM [NGroup_VTL_DB].[dbo].[News] WITH (NOLOCK) INNER JOIN [dbo].[GenericTypes] WITH (NOLOCK)
	ON [dbo].[News].[TypeID] = [dbo].[GenericTypes].[ID]
	WHERE 
		[dbo].[News].[IsActive] <> 0
		AND [dbo].[GenericTypes].[UniqueString] = 'tin-tuc'	
		AND [dbo].[GenericTypes].[IsActive] <> 0
	ORDER BY [dbo].[News].[CreatedAt] DESC, [dbo].[News].[LastUpdatedAt] DESC
END











GO


